"""
汽车和加油记录相关的异步 CRUD 操作
"""

from datetime import datetime

from sqlalchemy import desc, func, select
from sqlalchemy.ext.asyncio import AsyncSession

from src.models.family_model import Family
from src.models.vehicle_model import Vehicle, VehicleFuelRecord
from src.schemas.vehicle_schema import FuelConsumptionStats


# Vehicle 模型的 CRUD 操作
async def get_vehicle_by_id(
    db: AsyncSession, vehicle_id: int, family_id: int
) -> Vehicle | None:
    """根据 ID 获取车辆"""
    result = await db.execute(
        select(Vehicle).where(Vehicle.id == vehicle_id, Vehicle.family_id == family_id)
    )
    vehicle = result.scalar_one_or_none()

    # 获取家庭名称
    if vehicle:
        family_result = await db.execute(select(Family).where(Family.id == family_id))
        family = family_result.scalar_one_or_none()
        vehicle.family_name = family.name if family else None

    return vehicle


async def get_vehicle_by_license_plate(
    db: AsyncSession, license_plate: str, family_id: int
) -> Vehicle | None:
    """根据车牌号获取车辆"""
    result = await db.execute(
        select(Vehicle).where(
            Vehicle.license_plate == license_plate, Vehicle.family_id == family_id
        )
    )
    return result.scalar_one_or_none()


async def get_vehicles(
    db: AsyncSession,
    family_id: int,
    skip: int = 0,
    limit: int = 100,
    is_active: bool | None = None,
) -> tuple[list[Vehicle], int]:
    """获取车辆列表"""
    query = select(Vehicle).where(Vehicle.family_id == family_id)

    if is_active is not None:
        query = query.where(Vehicle.is_active == is_active)

    # 获取总数
    count_query = select(func.count()).select_from(query.subquery())
    total_result = await db.execute(count_query)
    total = total_result.scalar() or 0

    # 添加排序和分页
    query = query.order_by(desc(Vehicle.created_at)).offset(skip).limit(limit)
    result = await db.execute(query)
    vehicles = list(result.scalars().all())

    # 获取家庭名称并添加到每个车辆
    if vehicles:
        family_result = await db.execute(select(Family).where(Family.id == family_id))
        family = family_result.scalar_one_or_none()
        family_name = family.name if family else None

        for vehicle in vehicles:
            vehicle.family_name = family_name

    return vehicles, total


async def create_vehicle(
    db: AsyncSession,
    family_id: int,
    name: str,
    brand: str,
    model: str,
    license_plate: str,
    year: int | None = None,
    is_active: bool = True,
    notes: str | None = None,
) -> Vehicle:
    """创建车辆"""
    # 获取家庭名称
    family_result = await db.execute(select(Family).where(Family.id == family_id))
    family = family_result.scalar_one_or_none()
    family_name = family.name if family else None

    db_vehicle = Vehicle(
        family_id=family_id,
        family_name=family_name,
        name=name,
        brand=brand,
        model=model,
        license_plate=license_plate,
        year=year,
        is_active=is_active,
        notes=notes,
    )
    db.add(db_vehicle)
    await db.commit()
    await db.refresh(db_vehicle)

    return db_vehicle


async def update_vehicle(
    db: AsyncSession, vehicle_id: int, family_id: int, **kwargs
) -> Vehicle | None:
    """更新车辆"""
    vehicle = await get_vehicle_by_id(db, vehicle_id, family_id)
    if not vehicle:
        return None

    # 更新字段
    for key, value in kwargs.items():
        if hasattr(vehicle, key):
            setattr(vehicle, key, value)

    await db.commit()
    await db.refresh(vehicle)
    return vehicle


async def delete_vehicle(db: AsyncSession, vehicle_id: int, family_id: int) -> bool:
    """删除车辆"""
    vehicle = await get_vehicle_by_id(db, vehicle_id, family_id)
    if not vehicle:
        return False

    # 检查是否有关联的加油记录
    fuel_record_result = await db.execute(
        select(VehicleFuelRecord).where(
            VehicleFuelRecord.vehicle_id == vehicle_id,
            VehicleFuelRecord.family_id == family_id,
        )
    )
    fuel_records = fuel_record_result.scalars().all()
    if fuel_records:
        return False

    await db.delete(vehicle)
    await db.commit()
    return True


async def get_active_vehicles(db: AsyncSession, family_id: int) -> list[Vehicle]:
    """获取活跃车辆列表"""
    vehicles, _ = await get_vehicles(db, family_id, is_active=True)
    return vehicles


# VehicleFuelRecord 模型的 CRUD 操作
async def get_fuel_record_by_id(
    db: AsyncSession, record_id: int, family_id: int
) -> VehicleFuelRecord | None:
    """根据 ID 获取加油记录"""
    result = await db.execute(
        select(VehicleFuelRecord).where(
            VehicleFuelRecord.id == record_id, VehicleFuelRecord.family_id == family_id
        )
    )
    record = result.scalar_one_or_none()

    # 获取家庭名称和车辆名称
    if record:
        # 获取家庭名称
        family_result = await db.execute(select(Family).where(Family.id == family_id))
        family = family_result.scalar_one_or_none()
        record.family_name = family.name if family else None

        # 获取车辆名称
        vehicle_result = await db.execute(
            select(Vehicle).where(
                Vehicle.id == record.vehicle_id, Vehicle.family_id == family_id
            )
        )
        vehicle = vehicle_result.scalar_one_or_none()
        record.vehicle_name = vehicle.name if vehicle else None

    return record


async def get_fuel_records(
    db: AsyncSession,
    family_id: int,
    skip: int = 0,
    limit: int = 100,
    start_date: datetime | None = None,
    end_date: datetime | None = None,
    gas_station_type: str | None = None,
    vehicle_id: int | None = None,
) -> tuple[list[VehicleFuelRecord], int]:
    """获取加油记录列表"""
    query = select(VehicleFuelRecord).where(VehicleFuelRecord.family_id == family_id)

    # 添加过滤条件
    if start_date:
        query = query.where(VehicleFuelRecord.refuel_date >= start_date)
    if end_date:
        query = query.where(VehicleFuelRecord.refuel_date <= end_date)
    if gas_station_type:
        query = query.where(VehicleFuelRecord.gas_station_type == gas_station_type)
    if vehicle_id:
        query = query.where(VehicleFuelRecord.vehicle_id == vehicle_id)

    # 获取总数
    count_query = select(func.count()).select_from(query.subquery())
    total_result = await db.execute(count_query)
    total = total_result.scalar() or 0

    # 添加排序和分页
    query = (
        query.order_by(desc(VehicleFuelRecord.refuel_date)).offset(skip).limit(limit)
    )
    result = await db.execute(query)
    records = list(result.scalars().all())

    # 获取家庭名称和车辆名称
    if records:
        # 获取家庭名称
        family_result = await db.execute(select(Family).where(Family.id == family_id))
        family = family_result.scalar_one_or_none()
        family_name = family.name if family else None

        # 获取所有相关车辆
        vehicle_ids = list(set(record.vehicle_id for record in records))
        vehicle_query = select(Vehicle).where(
            Vehicle.id.in_(vehicle_ids), Vehicle.family_id == family_id
        )
        vehicle_result = await db.execute(vehicle_query)
        vehicles = {v.id: v.name for v in vehicle_result.scalars().all()}

        # 添加名称到记录
        for record in records:
            record.family_name = family_name
            record.vehicle_name = vehicles.get(record.vehicle_id)

    return records, total


async def get_fuel_records_by_vehicle(
    db: AsyncSession, vehicle_id: int, family_id: int, skip: int = 0, limit: int = 100
) -> tuple[list[VehicleFuelRecord], int]:
    """根据车辆ID获取加油记录"""
    query = select(VehicleFuelRecord).where(
        VehicleFuelRecord.family_id == family_id,
        VehicleFuelRecord.vehicle_id == vehicle_id,
    )

    # 获取总数
    count_query = select(func.count()).select_from(query.subquery())
    total_result = await db.execute(count_query)
    total = total_result.scalar() or 0

    # 添加排序和分页
    query = (
        query.order_by(desc(VehicleFuelRecord.refuel_date)).offset(skip).limit(limit)
    )
    result = await db.execute(query)
    records = list(result.scalars().all())

    return records, total


async def get_latest_fuel_record(
    db: AsyncSession, family_id: int, vehicle_id: int | None = None
) -> VehicleFuelRecord | None:
    """获取最新的加油记录"""
    query = select(VehicleFuelRecord).where(VehicleFuelRecord.family_id == family_id)

    if vehicle_id:
        query = query.where(VehicleFuelRecord.vehicle_id == vehicle_id)

    result = await db.execute(
        query.order_by(desc(VehicleFuelRecord.refuel_date)).limit(1)
    )
    return result.scalar_one_or_none()


async def calculate_fuel_consumption(
    db: AsyncSession, current_record: VehicleFuelRecord
) -> float | None:
    """计算油耗(升/100公里)"""
    # 获取同一辆车的上一次加油记录
    result = await db.execute(
        select(VehicleFuelRecord)
        .where(
            VehicleFuelRecord.family_id == current_record.family_id,
            VehicleFuelRecord.vehicle_id == current_record.vehicle_id,
            VehicleFuelRecord.refuel_date < current_record.refuel_date,
        )
        .order_by(desc(VehicleFuelRecord.refuel_date))
        .limit(1)
    )

    previous_record = result.scalar_one_or_none()

    if previous_record:
        # 计算行驶里程
        distance = current_record.current_mileage - previous_record.current_mileage
        if distance > 0:
            # 计算油耗:加油量 / 行驶里程 * 100
            fuel_consumption = (current_record.fuel_amount / distance) * 100
            return round(fuel_consumption, 2)

    return None


async def create_fuel_record(
    db: AsyncSession,
    family_id: int,
    vehicle_id: int,
    current_mileage: float,
    fuel_amount: float,
    fuel_cost: float,
    fuel_price: float,
    gas_station_type: str,
    refuel_date: datetime,
    notes: str | None = None,
) -> VehicleFuelRecord:
    """创建加油记录"""
    # 验证车辆是否存在且属于该家庭
    vehicle = await get_vehicle_by_id(db, vehicle_id, family_id)
    if not vehicle:
        raise ValueError("车辆不存在或不属于当前家庭")

    # 获取家庭名称
    family_result = await db.execute(select(Family).where(Family.id == family_id))
    family = family_result.scalar_one_or_none()
    family_name = family.name if family else None

    # 获取车辆名称
    vehicle_name = vehicle.name if vehicle else None

    # 创建记录
    db_record = VehicleFuelRecord(
        family_id=family_id,
        family_name=family_name,
        vehicle_id=vehicle_id,
        vehicle_name=vehicle_name,
        current_mileage=current_mileage,
        fuel_amount=fuel_amount,
        fuel_cost=fuel_cost,
        fuel_price=fuel_price,
        gas_station_type=gas_station_type,
        refuel_date=refuel_date,
        notes=notes,
    )

    db.add(db_record)
    await db.commit()
    await db.refresh(db_record)

    # 计算油耗
    fuel_consumption = await calculate_fuel_consumption(db, db_record)
    if fuel_consumption:
        db_record.fuel_consumption = fuel_consumption
        await db.commit()
        await db.refresh(db_record)

    return db_record


async def update_fuel_record(
    db: AsyncSession, record_id: int, family_id: int, **kwargs
) -> VehicleFuelRecord | None:
    """更新加油记录"""
    # 获取加油记录
    record = await get_fuel_record_by_id(db, record_id, family_id)
    if not record:
        return None

    # 更新字段
    for key, value in kwargs.items():
        if hasattr(record, key):
            setattr(record, key, value)

    await db.commit()
    await db.refresh(record)

    # 重新计算油耗
    fuel_consumption = await calculate_fuel_consumption(db, record)
    record.fuel_consumption = fuel_consumption
    await db.commit()
    await db.refresh(record)

    return record


async def delete_fuel_record(db: AsyncSession, record_id: int, family_id: int) -> bool:
    """删除加油记录"""
    # 获取加油记录
    record = await get_fuel_record_by_id(db, record_id, family_id)
    if not record:
        return False

    await db.delete(record)
    await db.commit()
    return True


async def get_fuel_consumption_stats(
    db: AsyncSession,
    family_id: int,
    start_date: datetime | None = None,
    end_date: datetime | None = None,
    vehicle_id: int | None = None,
) -> FuelConsumptionStats | None:
    """获取油耗统计信息"""
    query = select(VehicleFuelRecord).where(VehicleFuelRecord.family_id == family_id)

    # 添加过滤条件
    if start_date:
        query = query.where(VehicleFuelRecord.refuel_date >= start_date)
    if end_date:
        query = query.where(VehicleFuelRecord.refuel_date <= end_date)
    if vehicle_id:
        query = query.where(VehicleFuelRecord.vehicle_id == vehicle_id)

    result = await db.execute(query)
    records = list(result.scalars().all())

    if not records:
        return None

    # 按车辆和日期分组
    vehicle_records = {}
    for record in records:
        if record.vehicle_id not in vehicle_records:
            vehicle_records[record.vehicle_id] = []
        vehicle_records[record.vehicle_id].append(record)

    # 计算统计数据
    total_fuel_cost = sum(float(record.fuel_cost) for record in records)
    total_fuel_amount = sum(float(record.fuel_amount) for record in records)
    record_count = len(records)

    # 计算总行驶里程和平均油耗
    total_distance = 0.0
    total_fuel_consumption = 0.0
    consumption_count = 0

    for vehicle_id, veh_records in vehicle_records.items():
        # 按时间排序
        veh_records.sort(key=lambda x: x.refuel_date)

        for i in range(1, len(veh_records)):
            distance = float(veh_records[i].current_mileage) - float(
                veh_records[i - 1].current_mileage
            )
            if distance > 0 and veh_records[i].fuel_consumption:
                total_distance += distance
                total_fuel_consumption += float(veh_records[i].fuel_consumption or 0.0)
                consumption_count += 1

    # 计算平均值
    avg_fuel_consumption = (
        total_fuel_consumption / consumption_count if consumption_count > 0 else 0
    )
    avg_fuel_price = total_fuel_cost / total_fuel_amount if total_fuel_amount > 0 else 0

    return FuelConsumptionStats(
        avg_fuel_consumption=round(avg_fuel_consumption, 2),
        total_fuel_cost=round(total_fuel_cost, 2),
        total_fuel_amount=round(total_fuel_amount, 2),
        total_distance=round(total_distance, 2),
        record_count=record_count,
        avg_fuel_price=round(avg_fuel_price, 2),
    )


async def get_fuel_consumption_stats_by_vehicle(
    db: AsyncSession,
    vehicle_id: int,
    family_id: int,
    start_date: datetime | None = None,
    end_date: datetime | None = None,
) -> FuelConsumptionStats | None:
    """获取指定车辆的油耗统计信息"""
    query = select(VehicleFuelRecord).where(
        VehicleFuelRecord.family_id == family_id,
        VehicleFuelRecord.vehicle_id == vehicle_id,
    )

    # 添加过滤条件
    if start_date:
        query = query.where(VehicleFuelRecord.refuel_date >= start_date)
    if end_date:
        query = query.where(VehicleFuelRecord.refuel_date <= end_date)

    result = await db.execute(query)
    records = list(result.scalars().all())

    if not records:
        return None

    # 按时间排序
    records.sort(key=lambda x: x.refuel_date)

    # 计算统计数据
    total_fuel_cost = sum(float(record.fuel_cost) for record in records)
    total_fuel_amount = sum(float(record.fuel_amount) for record in records)
    record_count = len(records)

    # 计算总行驶里程和平均油耗
    total_distance = 0.0
    total_fuel_consumption = 0.0
    consumption_count = 0

    for i in range(1, len(records)):
        distance = float(records[i].current_mileage) - float(
            records[i - 1].current_mileage
        )
        if distance > 0 and records[i].fuel_consumption:
            total_distance += distance
            total_fuel_consumption += float(records[i].fuel_consumption or 0.0)
            consumption_count += 1

    # 计算平均值
    avg_fuel_consumption = (
        total_fuel_consumption / consumption_count if consumption_count > 0 else 0
    )
    avg_fuel_price = total_fuel_cost / total_fuel_amount if total_fuel_amount > 0 else 0

    return FuelConsumptionStats(
        avg_fuel_consumption=round(avg_fuel_consumption, 2),
        total_fuel_cost=round(total_fuel_cost, 2),
        total_fuel_amount=round(total_fuel_amount, 2),
        total_distance=round(total_distance, 2),
        record_count=record_count,
        avg_fuel_price=round(avg_fuel_price, 2),
    )


async def get_fuel_records_by_station_type(
    db: AsyncSession,
    family_id: int,
    gas_station_type: str,
    skip: int = 0,
    limit: int = 100,
) -> tuple[list[VehicleFuelRecord], int]:
    """根据加油站类型获取加油记录"""
    query = select(VehicleFuelRecord).where(
        VehicleFuelRecord.family_id == family_id,
        VehicleFuelRecord.gas_station_type == gas_station_type,
    )

    # 获取总数
    count_query = select(func.count()).select_from(query.subquery())
    total_result = await db.execute(count_query)
    total = total_result.scalar() or 0

    # 添加排序和分页
    query = (
        query.order_by(desc(VehicleFuelRecord.refuel_date)).offset(skip).limit(limit)
    )
    result = await db.execute(query)
    records = list(result.scalars().all())

    return records, total


async def get_fuel_cost_summary(
    db: AsyncSession,
    family_id: int,
    start_date: datetime | None = None,
    end_date: datetime | None = None,
    vehicle_id: int | None = None,
) -> dict:
    """获取加油费用汇总"""
    query = select(VehicleFuelRecord).where(VehicleFuelRecord.family_id == family_id)

    # 添加过滤条件
    if start_date:
        query = query.where(VehicleFuelRecord.refuel_date >= start_date)
    if end_date:
        query = query.where(VehicleFuelRecord.refuel_date <= end_date)
    if vehicle_id:
        query = query.where(VehicleFuelRecord.vehicle_id == vehicle_id)

    result = await db.execute(query)
    records = list(result.scalars().all())

    if not records:
        return {
            "total_cost": 0,
            "total_amount": 0,
            "avg_price": 0,
            "station_summary": {},
        }

    total_cost = sum(float(record.fuel_cost) for record in records)
    total_amount = sum(float(record.fuel_amount) for record in records)
    avg_price = total_cost / total_amount if total_amount > 0 else 0

    # 按加油站类型汇总
    station_summary = {}
    for record in records:
        station_type = record.gas_station_type
        if station_type not in station_summary:
            station_summary[station_type] = {
                "count": 0,
                "total_cost": 0.0,
                "total_amount": 0.0,
            }
        station_summary[station_type]["count"] += 1
        station_summary[station_type]["total_cost"] += float(record.fuel_cost or 0.0)
        station_summary[station_type]["total_amount"] += float(
            record.fuel_amount or 0.0
        )

    return {
        "total_cost": round(total_cost, 2),
        "total_amount": round(total_amount, 2),
        "avg_price": round(avg_price, 2),
        "station_summary": station_summary,
    }
